﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Diagnostics;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

using AMS.Database;
using AMS.Enumerations;
using AMS.LINQ;
using AMS.UI;

namespace AMS.Business
{
    public class RequestClass
    {
        int requestNumber = 0;
        int requestor;
        enuStatus status = enuStatus.Nieuw;
        //int internalStatus = 1;
        String product = String.Empty;
        String seals = String.Empty;
        String weight = String.Empty;
        String references = String.Empty;
        String droppedOffBy = String.Empty;
        DateTime sampleReceived = DateTime.Now;
        DateTime instructionsReceived = DateTime.Now;
        int paper = 0;
        int glass = 0;
        int linnen = 0;
        int plastic = 0;
        List<Int32> analysisMethods = new List<Int32>();
        List<AnalysisMethodUIClass> analysisMethodClass = new List<AnalysisMethodUIClass>();

        bool isDirty = false;
        bool isNew = false;

        #region properties

        public List<AnalysisMethodUIClass> AnalysisMethods
        {
            get
            {
                return analysisMethodClass;
            }
        }

        public String Paper
        {
            get
            {
                return paper.ToString();
            }
            set
            {
                if (value == paper.ToString()) { isDirty = false; return; }
                if (value == String.Empty)
                {
                    paper = 0;
                }
                else
                {
                    if (!Int32.TryParse(value, out paper))
                    {
                        throw new Exception("Geen geldige waarde voor papier.");
                    }
                }
                isDirty = true;
            }
        }

        public String Glass
        {
            get
            {
                return glass.ToString();
            }
            set
            {
                if (value == glass.ToString()) { isDirty = false; return; }
                if (value == String.Empty)
                {
                    glass = 0;
                }
                else
                {
                    if (!Int32.TryParse(value, out glass))
                    {
                        throw new Exception("Geen geldige waarde voor glas.");
                    }
                }
                isDirty = true;
            }
        }

        public String Linnen
        {
            get
            {
                return linnen.ToString();
            }
            set
            {
                if (value == linnen.ToString()) { isDirty = false; return; }
                if (value == String.Empty)
                {
                    linnen = 0;
                }
                else
                {
                    if (!Int32.TryParse(value, out linnen))
                    {
                        throw new Exception("Geen geldige waarde voor linnen.");
                    }
                }
                isDirty = true;
            }
        }

        public String Plastic
        {
            get
            {
                return plastic.ToString();
            }
            set
            {
                if (value == plastic.ToString()) { isDirty = false; return; }
                if (value == String.Empty)
                {
                    plastic = 0;
                }
                else
                {
                    if (!Int32.TryParse(value, out plastic))
                    {
                        throw new Exception("Geen geldige waarde voor plastic.");
                    }
                }
                isDirty = true;
            }
        }

        public bool IsDirty
        {
            get
            {
                return isDirty;
            }
        }

        public bool IsNew
        { get { return isNew; } }

        public int RequestNumber
        {
            get
            {
                return requestNumber;
            }
        }

        public enuStatus Status
        {
            get
            {
                return status;
            }
        }

        public int Requestor
        {
            get { return requestor; }
            set 
            {
                requestor = value;
                isDirty = true;
            }
        }

        public String Product
        {
            get{return product;}
            set
            {
                if (value.Length == 0 || value.Length > 50)
                {
                    throw new Exception("Ongeldige productwaarde.");
                }
                else
                {
                    if (value == product) { isDirty = false; return; }
                    product = value;
                }
                isDirty = true;
            }
        }

        public String Seals
        {
            get { return seals;}
            set
            {
                if (value.Length == 0 || value.Length > 50)
                {
                    throw new Exception("Ongeldige zegelwaarde");
                }
                else
                {
                    if (value == seals) { isDirty = false; return; }
                    seals = value;
                }
                isDirty = true;
            }
        }

        public String Weight
        {
            get { return weight;}
            set
            {
                if (value.Length == 0 || value.Length > 50)
                {
                    throw new Exception("Ongeldige gewichtwaarde.");
                }
                else
                {
                    if (value == weight) { isDirty = false; return; }
                    weight = value;
                }
                isDirty = true;
            }
        }

        public String References
        {
            get { return references; }
            set
            {
                if (value.Length == 0 || value.Length > 50)
                {
                    throw new Exception("Ongeldige referentiewaarde.");
                }
                else
                {
                    if (value == references) { isDirty = false; return; }
                    references = value;
                }
                isDirty = true;
            }
        }

        public String DroppedOffBy
        {
            get { return droppedOffBy; }
            set
            {
                if (value.Length == 0 || value.Length > 50)
                {
                    throw new Exception("Ongeldige waarde.");
                }
                else
                {
                    if (value == droppedOffBy) { isDirty = false; return; }
                    droppedOffBy = value;
                }
                isDirty = true;
            }
        }

        public DateTime SampleReceived
        {
            get { return sampleReceived; }
            set
            {
                if (value == sampleReceived) { isDirty = false; return; }
                sampleReceived = value;
                isDirty = true;
            }
        }

        public DateTime InstructionsReceived
        {
            get { return instructionsReceived; }
            set
            {
                if (value == instructionsReceived) { isDirty = false; return; }
                instructionsReceived = value;
                isDirty = true;
            }
        }
        #endregion

        #region public methods
        public RequestClass()
        { 
            isNew = true;
            //internalStatus = 1;
            isDirty = false;
            status = enuStatus.Nieuw;
            instructionsReceived = DateTime.Now;
            sampleReceived = DateTime.Now;
        }

        public void AddAnalysisMethod(Int32 analysisMethodID)
        {
            if (!analysisMethods.Contains(analysisMethodID))
            {
                analysisMethods.Add(analysisMethodID);
                isDirty = true;
            }
            else
            {
                isDirty = false;
            }
        }

        public void RemoveAnalysisMethod(Int32 analysisMethodID)
        { 
            analysisMethods.Remove(analysisMethodID);
            isDirty = true;
        }

        public void ClearAnalysisMethod()
        {
            analysisMethods = null;
            analysisMethods = new List<int>();
        }

        public void Save()
        {
            try
            {
                if (isNew)
                {
                    AddRequest();
                }
                else if(isDirty)
                {
                    UpdateRequest();
                    DeletePackageAmount();
                    DeleteAnalysisMethodToRequest();
                }

                if (isNew || isDirty)
                {
                    AddPackageAmount(enuPackage.Glas, glass);
                    AddPackageAmount(enuPackage.Linnen, linnen);
                    AddPackageAmount(enuPackage.Paper, paper);
                    AddPackageAmount(enuPackage.Plastic, plastic);

                    for (int i = 0; i < analysisMethods.Count; i++)
                    {
                        AddAnalysisMethodToRequest(analysisMethods[i]);
                    }
                }

                isNew = false;
                isDirty = false;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public void Delete()
        {
            try
            {
                if (!isNew)
                {
                    DeleteRequest();   
                }
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public void Load(Int32 ID)
        {
            try
            {
                requestNumber = ID;

                LoadRequest();
                LoadPackages();
                LoadAnalysisMethodRequest();

                isNew = false;
                isDirty = false;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool CheckRequest(Int32 RequestID)
        {
            // controleert of deze aanvraag al bestaat
            // zou niet meer dan 1 keer mogen bestaan

            try
            {
                var request = (from r in Common.Request().Requests
                              where r.RequestNumber == RequestID
                              select r).Count();

                if (request == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region private methods

        #region DataAccess

        private void AddRequest()
        {
            try
            {
                #region Old code
                //comm.CommandType = CommandType.StoredProcedure;
                //comm.CommandText = "AddRequest";
                //comm.Parameters.Clear();

                //SqlParameter param = new SqlParameter();
                //param.ParameterName = "@RetVal";
                //param.SqlDbType = SqlDbType.Int;
                //param.Direction = ParameterDirection.ReturnValue;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "Product";
                //param.Value = product;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "Reference";
                //param.Value = references;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "Weight";
                //param.Value = weight;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "Seals";
                //param.Value = seals;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "DroppedOffBy";
                //param.Value = droppedOffBy;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "SampleReceived";
                //param.Value = sampleReceived;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "InstructionsReceived";
                //param.Value = instructionsReceived;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "RequestStatus";
                //param.Value = status;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "RequestorID";
                //param.Value = requestor;
                //comm.Parameters.Add(param);

                //db.ExecuteNonQuery(comm,trans);
                //requestNumber = Convert.ToInt32(comm.Parameters["@RetVal"].Value);

                //if (requestNumber == -1)
                //{
                //    throw new Exception("Er heeft zich een fout voorgedaan bij het toevoegen van een aanvraag.");
                //}
                #endregion

                Request newRequest = new Request();
                newRequest.DroppedOffBy = droppedOffBy;
                newRequest.InstructionsReceived = instructionsReceived;
                newRequest.Product = product;
                newRequest.Reference = references;
                newRequest.RequestorID_FK = requestor.ToString();
                newRequest.SampleReceived = sampleReceived;
                newRequest.Seals = seals;
                newRequest.Weight = weight;
                newRequest.RequestStatus_FK = 1; // Status = Nieuw

                Common.Request().Requests.InsertOnSubmit(newRequest);
                Common.Request().SubmitChanges();

                requestNumber = newRequest.RequestNumber;
            }
            catch (Exception)
            {

                throw;
            }
        }

        private void AddPackageAmount(enuPackage package, int amount)
        {
            try
            {
                #region Old code
                //comm.CommandType = CommandType.StoredProcedure;
                //comm.CommandText = "AddPackageAmount";
                //comm.Parameters.Clear();

                //SqlParameter param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "Amount";
                //param.Value = amount;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "RequestNumber";
                //param.Value = requestNumber;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "PackageID";
                //param.Value = package;
                //comm.Parameters.Add(param);

                //int ret = db.ExecuteNonQuery(comm, trans);

                //if (ret == 0)
                //{
                //    throw new Exception("Er is een probleem opgetreden bij het toevoegen van een verpakking.");
                //}
                #endregion

                Packaging newPackaging = new Packaging();
                newPackaging.RequestNumber_FK = requestNumber;
                newPackaging.Amount = amount;
                newPackaging.PackagingSettingID_FK = Convert.ToInt32(package);
                Common.Packaging().Packagings.InsertOnSubmit(newPackaging);
                Common.Packaging().SubmitChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        private void DeleteAnalysisMethodToRequest()
        {
            try
            {
                var toBeDeleted = from request in Common.Request().Requests
                                  where request.RequestNumber == requestNumber
                                  select request;

                Common.Request().Requests.DeleteOnSubmit(toBeDeleted.SingleOrDefault());
                Common.Request().SubmitChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        private void AddAnalysisMethodToRequest(Int32 ID)
        {
            try
            {
                #region Old code
                //comm.CommandType = CommandType.StoredProcedure;
                //comm.CommandText = "AddRequestAnalysisMethod";
                //comm.Parameters.Clear();

                //SqlParameter param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "RequestNumber";
                //param.Value = requestNumber;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "AnalysisMethodID";
                //param.Value = ID;
                //comm.Parameters.Add(param);

                //int ret = db.ExecuteNonQuery(comm,trans);
                #endregion

                Request_AnalysisMethod ra = new Request_AnalysisMethod();
                ra.RequestNumber_FK = requestNumber;
                ra.AnalysisMethodID_FK = ID;

                Common.Request_AnalysisMethod().Request_AnalysisMethods.InsertOnSubmit(ra);
                Common.Request_AnalysisMethod().SubmitChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        private void UpdateRequest()
        {
            try
            {
                #region Old code
                //comm = new SqlCommand();
                //comm.CommandType = CommandType.StoredProcedure;
                //comm.CommandText = "UpdateRequest";
                //comm.Parameters.Clear();

                //SqlParameter param = new SqlParameter();
                //param.ParameterName = "RequestNumber";
                //param.Direction = ParameterDirection.Input;
                //param.Value = requestNumber;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "Product";
                //param.Value = product;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "Reference";
                //param.Value = references;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "Weight";
                //param.Value = weight;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "Seals";
                //param.Value = seals;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "DroppedOffBy";
                //param.Value = droppedOffBy;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "SampleReceived";
                //param.Value = sampleReceived;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "InstructionsReceived";
                //param.Value = instructionsReceived;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "RequestStatus";
                //param.Value = status;
                //comm.Parameters.Add(param);

                //param = new SqlParameter();
                //param.Direction = ParameterDirection.Input;
                //param.ParameterName = "RequestorID";
                //param.Value = requestor.ToString();
                //comm.Parameters.Add(param);

                //db.ExecuteNonQuery(comm,trans);
                #endregion

                var rToBeUpdated = from r in Common.Request().Requests
                                   where r.RequestNumber == requestNumber
                                   select r;

                foreach (var item in rToBeUpdated)
                {
                    item.DroppedOffBy = droppedOffBy;
                    item.InstructionsReceived = instructionsReceived;
                    item.Product = product;
                    item.Reference = references;
                    item.RequestorID_FK = requestor.ToString();
                    item.SampleReceived = sampleReceived;
                    item.Seals = seals;
                    item.Weight = weight;
                }

                Common.Request().SubmitChanges();
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
                throw;
            }
        }

        private void DeletePackageAmount()
        {
            try
            {
                #region Old code
                //comm = new SqlCommand();
                //comm.CommandType = CommandType.StoredProcedure;
                //comm.CommandText = "DeletePackageAmount";
                //comm.Parameters.Clear();

                //SqlParameter param = new SqlParameter();
                //param.ParameterName = "RequestNumber";
                //param.Direction = ParameterDirection.Input;
                //param.Value = requestNumber;
                //comm.Parameters.Add(param);

                //db.ExecuteNonQuery(comm, trans);
                #endregion

                var pToBeDeleted = from p in Common.Packaging().Packagings
                                   where p.RequestNumber_FK == requestNumber
                                   select p;

                Common.Packaging().Packagings.DeleteOnSubmit(pToBeDeleted.SingleOrDefault());
                Common.Packaging().SubmitChanges();
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        private void DeleteRequest()
        {
            try
            {
                // Replaces DeleteRequest stored procedure
                // In this stored procedure we delete from the following tables:
                // - Request_AnalysisMethod
                // - Request
                var raToBeDeleted = from ra in Common.Request_AnalysisMethod().Request_AnalysisMethods
                                    where ra.RequestNumber_FK == requestNumber
                                    select ra;

                foreach (var item in raToBeDeleted)
                {
                    Common.Request_AnalysisMethod().Request_AnalysisMethods.DeleteOnSubmit(item);
                }

                var rToBeDeleted = from r in Common.Request().Requests
                                   where r.RequestNumber == requestNumber
                                   select r;
                Common.Request().Requests.DeleteOnSubmit(rToBeDeleted.SingleOrDefault());

                // Replaces DeletePackageAmount stored procedure
                // In this stored procedure we delete from the following tables:
                // - Packaging
                var pToBeDeleted = from p in Common.Packaging().Packagings
                                   where p.RequestNumber_FK == requestNumber
                                   select p;
                foreach (var item in pToBeDeleted)
                {
                    Common.Packaging().Packagings.DeleteOnSubmit(item);    
                }
                
                // Submit all the changes
                Common.Packaging().SubmitChanges();
                Common.Request_AnalysisMethod().SubmitChanges();
                Common.Request().SubmitChanges();

            }
            catch (Exception)
            {

                throw;
            }
        }

        private void LoadRequest()
        {
            try
            {
                var request = from r in Common.Request().Requests
                              where r.RequestNumber == requestNumber
                              select r;

                foreach (var item in request)
                {
                    droppedOffBy = item.DroppedOffBy;
                    instructionsReceived = (DateTime)item.InstructionsReceived;
                    product = item.Product;
                    references = item.Reference;
                    sampleReceived = (DateTime)item.SampleReceived;
                    seals = item.Seals;
                    weight = item.Weight;
                    requestor = Convert.ToInt32(item.RequestorID_FK);
                }

            }
            catch (Exception)
            {
                throw;
            }
        }

        private void LoadPackages()
        {
            try
            {
                var packages = from p in Common.Packaging().Packagings
                               where p.RequestNumber_FK == requestNumber
                               select p;

                foreach (var item in packages)
                {
                    switch (item.PackagingSettingID_FK)
                    {
                        // papier
                        case 1:
                            paper = item.Amount;
                            break;
                        case 2:
                            glass = item.Amount;
                            break;
                        case 3:
                            linnen = item.Amount;
                            break;
                        case 4:
                            plastic = item.Amount;
                            break;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private void LoadAnalysisMethodRequest()
        {
            try
            {
                var am = from ar in Common.Request_AnalysisMethod().Request_AnalysisMethods
                                      where ar.RequestNumber_FK == requestNumber
                                      select ar;

                foreach (var item in am)
                {
                    analysisMethods.Add(item.AnalysisMethodID_FK);
                    analysisMethodClass.Add(new AnalysisMethodUIClass(item.AnalysisMethodID_FK, null));
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #endregion
    }
}
